package com.xinchuang.service.impl;

import com.alibaba.fastjson.JSON;
import com.xinchuang.comment.KeyWord;
import com.xinchuang.comment.R;
import com.xinchuang.domain.vo.TempVo;
import com.xinchuang.domain.vo.qr.*;
import com.xinchuang.service.ExportQROutQuotationService;
import com.xinchuang.service.IMoldSelectionService;
import com.xinchuang.utils.BigdecimalUtil;
import com.xinchuang.utils.DateUtils;
import com.xinchuang.utils.PoiUtils;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.compress.utils.Lists;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletResponse;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.TimeZone;

@Slf4j
@Service
@RequiredArgsConstructor
public class ExportQROutQuotationServiceImpl implements ExportQROutQuotationService {
    private final KeyWord keyWord;

    @Autowired
    private IMoldSelectionService iMoldSelectionService;

    @Override
    public String exportQROutQuotation(String data, HttpServletResponse res) {
        log.info("奇瑞对外报价单导出start");
        SimpleDateFormat inputFormat = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'");
        inputFormat.setTimeZone(TimeZone.getTimeZone("UTC"));
        SimpleDateFormat outputFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        outputFormat.setTimeZone(TimeZone.getTimeZone("Asia/Shanghai"));
        R r = JSON.parseObject(data, R.class);
        QrQuotationVo normalOutQuotationResVo = JSON.parseObject(JSON.toJSONString(r.getData()), QrQuotationVo.class);
        String moldSelectionId = normalOutQuotationResVo.getMoldSelectionId();
        String projectName = iMoldSelectionService.getProjectName(moldSelectionId);
        // 文件名称
        String fileName = projectName + "项目-奇瑞对外报价单-" + DateUtils.dateTimeNow("yyyyMMdd-SSS");

        String resultPath = "file" + File.separator + fileName + ".xlsx";

        String os = System.getProperty("os.name");
        String filePath = "";
        if (os.contains("Linux")) {
            filePath = keyWord.getFileUrl() + File.separator + resultPath;
        } else {
            filePath = keyWord.getFileUrl() + File.separator + resultPath;
        }

        try {
            //创建一个工作簿
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet sheet = workbook.createSheet("报价汇总单");
            sheet.setZoom(115); // 设置默认缩放比例为100%
            sheet.setColumnWidth(0, 4 * 256);
            sheet.setColumnWidth(1, 14 * 256);
            sheet.setColumnWidth(2, 23 * 256);
            sheet.setColumnWidth(3, 6 * 256);
            sheet.setColumnWidth(4, 6 * 256);
            sheet.setColumnWidth(5, 4 * 256);
            sheet.setColumnWidth(6, 12 * 256);
            sheet.setColumnWidth(7, 8 * 256);
            sheet.setColumnWidth(8, 10 * 256);
            sheet.setColumnWidth(9, 8 * 256);
            sheet.setColumnWidth(10, 10 * 256);
            sheet.setColumnWidth(11, 8 * 256);
            sheet.setColumnWidth(12, 8 * 256);
            sheet.setColumnWidth(13, 9 * 256);
            sheet.setColumnWidth(14, 8 * 256);
            sheet.setColumnWidth(15, 9 * 256);
            sheet.setColumnWidth(16, 13 * 256);
            sheet.setColumnWidth(17, 4 * 256);

            // 创建两种字体样式，分别用于不同部分的文本
            XSSFFont fontBold = workbook.createFont();
            fontBold.setBold(true);
            fontBold.setFontName("宋体");
            fontBold.setFontHeightInPoints((short) 13);//设置字号

            XSSFFont fontNormal = workbook.createFont();
            fontNormal.setBold(false);
            fontNormal.setFontName("宋体");
            fontNormal.setFontHeightInPoints((short) 11);//设置字号

            XSSFRichTextString richTextString = new XSSFRichTextString();
            richTextString.append("                                       ", fontNormal);
            richTextString.append("报价汇总表", fontBold);// 使用粗体字体
            richTextString.append("                                     ", fontNormal);
            richTextString.append("单位：人民币/元，含13%增值税 ", fontNormal); // 使用普通字体
            // 合并单元格
            mergedRegion(sheet, 0, 0, 0, 17, true, "");

            // 创建一个单元格样式对象
            XSSFCellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

            getCell(getRow(sheet, 0), 0).setCellValue(richTextString);
            getCell(getRow(sheet, 0), 0).setCellStyle(cellStyle);


            // 设置数据title行
            String[] str11 = {"序号", "零件号", "零件名称", "类别", "型腔数", "模具\n数量", "加工\n材料费", "热处理费", "装配\n材料费",
                    "设计费", "加工费", "三坐标\n检测费", "管理费", "利润", "试模费", "运输费", "含税单价", "备注"};

            for (int i = 0; i < str11.length; i++) {
                getRow(sheet, 1).createCell(i).setCellValue(str11[i]);
            }
            NumberFormat formatter = new DecimalFormat("#0.00");
            //遍历
            int rowNum = 2;
            for (int i = 0; i < normalOutQuotationResVo.getDataList().size(); i++) {
                QrQuotationDetailVo vo = normalOutQuotationResVo.getDataList().get(i);
                getCell(getRow(sheet, rowNum), 0).setCellValue("" + (i + 1));
                getCell(getRow(sheet, rowNum), 1).setCellValue(vo.getCodeOfProduct());
                getCell(getRow(sheet, rowNum), 2).setCellValue(vo.getNameOfcodeOfProduct());
                getCell(getRow(sheet, rowNum), 3).setCellValue(vo.getTypeName());
                getCell(getRow(sheet, rowNum), 4).setCellValue(vo.getCavityCount());
                getCell(getRow(sheet, rowNum), 5).setCellValue(vo.getModuleCount());
                getCell(getRow(sheet, rowNum), 6).setCellValue(formatter.format(vo.getWorkMaterialFeeListTotal()));
                getCell(getRow(sheet, rowNum), 7).setCellValue(formatter.format(vo.getHeatTreatmentFeeListTotal()));
                getCell(getRow(sheet, rowNum), 8).setCellValue(formatter.format(vo.getAssemlyMaterialFeeListTotal()));
                getCell(getRow(sheet, rowNum), 9).setCellValue(formatter.format(vo.getDesignFeeListTotal()));
                getCell(getRow(sheet, rowNum), 10).setCellValue(formatter.format(vo.getManufacturingFeeListTotal()));
                getCell(getRow(sheet, rowNum), 11).setCellValue(formatter.format(vo.getCMMMeasureFeeListTotal()));
                getCell(getRow(sheet, rowNum), 12).setCellValue(formatter.format(vo.getManagementFeeListTotal()));
                getCell(getRow(sheet, rowNum), 13).setCellValue(formatter.format(vo.getProfitListTotal()));
                getCell(getRow(sheet, rowNum), 14).setCellValue(formatter.format(vo.getTryoutFeeListTotal()));
                getCell(getRow(sheet, rowNum), 15).setCellValue(formatter.format(vo.getTransportFeeListTotal()));
                getCell(getRow(sheet, rowNum), 16).setCellValue(formatter.format(vo.getTaxIncludedFeeListTotal()));
                getCell(getRow(sheet, rowNum), 17).setCellValue(vo.getRemark());
                rowNum = rowNum + 1;
            }
            mergedRegion(sheet, rowNum, rowNum, 0, 5, true, "汇总");

            getCell(getRow(sheet, rowNum), 6).setCellValue(formatter.format(normalOutQuotationResVo.getWorkMaterialFeeTotal()));
            getCell(getRow(sheet, rowNum), 7).setCellValue(formatter.format(normalOutQuotationResVo.getHeatTreatmentFeeTotal()));
            getCell(getRow(sheet, rowNum), 8).setCellValue(formatter.format(normalOutQuotationResVo.getAssemlyMaterialFeeTotal()));
            getCell(getRow(sheet, rowNum), 9).setCellValue(formatter.format(normalOutQuotationResVo.getDesignFeeTotal()));
            getCell(getRow(sheet, rowNum), 10).setCellValue(formatter.format(normalOutQuotationResVo.getManufacturingFeeTotal()));
            getCell(getRow(sheet, rowNum), 11).setCellValue(formatter.format(normalOutQuotationResVo.getCMMMeasureFeeTotal()));
            getCell(getRow(sheet, rowNum), 12).setCellValue(formatter.format(normalOutQuotationResVo.getManagementFeeTotal()));
            getCell(getRow(sheet, rowNum), 13).setCellValue(formatter.format(normalOutQuotationResVo.getProfitTotal()));
            getCell(getRow(sheet, rowNum), 14).setCellValue(formatter.format(normalOutQuotationResVo.getTryoutFeeTotal()));
            getCell(getRow(sheet, rowNum), 15).setCellValue(formatter.format(normalOutQuotationResVo.getTransportFeeTotal()));
            getCell(getRow(sheet, rowNum), 16).setCellValue(formatter.format(normalOutQuotationResVo.getTaxIncludedFeeTotal()));
            getCell(getRow(sheet, rowNum), 17).setCellValue(normalOutQuotationResVo.getRemark());

            for (int j = normalOutQuotationResVo.getDataList().size() + 1; j <= normalOutQuotationResVo.getDataList().size() + 9; j++) {
                for (int i = 0; i <= 17; i++) {
                    getCell(getRow(sheet, j), i).setCellStyle(removeBorder(workbook));
                }
            }
            rowNum = rowNum + 2;
            mergedRegionNoStyle(sheet, rowNum, rowNum, 0, 7, "说明：");
            rowNum = rowNum + 1;
            mergedRegionNoStyle(sheet, rowNum, rowNum, 0, 7, "1.具体模具数据信息请与我司技术部门或一级供应商联系确认；");
            mergedRegionNoStyle(sheet, rowNum, rowNum, 11, 16,  "签字Signature：" + normalOutQuotationResVo.getSignature());
            rowNum = rowNum + 1;
            mergedRegionNoStyle(sheet, rowNum, rowNum, 0, 7, "2.对任何一副模具不允许多报或漏报，否则视为无效；");
            rowNum = rowNum + 1;
            mergedRegionNoStyle(sheet, rowNum, rowNum, 0, 7, "3.报价包含13%增值税的人民币最终报价；");
            rowNum = rowNum + 1;
            mergedRegionNoStyle(sheet, rowNum, rowNum, 0, 7, "4.报价完全满足本次询价要求；");
            mergedRegionNoStyle(sheet, rowNum, rowNum, 11, 16, "日期Date：" + normalOutQuotationResVo.getDate());
            rowNum = rowNum + 1;
            mergedRegionNoStyle(sheet, rowNum, rowNum, 0, 7,  "5.单位为元，小数点保留2位；");

            //设置行高
            sheet.getRow(0).setHeight((short) (33.15 * 20));
            sheet.getRow(1).setHeight((short) (49.15 * 20));
            for (int i = 2; i < normalOutQuotationResVo.getDataList().size() + 3; i++) {
                getRow(sheet, i).setHeight((short) (26.05 * 20));
            }
            //处理列表展示样式
            for (int j = 0; j <= normalOutQuotationResVo.getDataList().size() + 2; j++) {
                getRow(sheet, j).forEach(c -> {
                    if(c.getRowIndex() == normalOutQuotationResVo.getDataList().size() + 2){
                        c.setCellStyle(createCellStyle(workbook,9));
                    }else{
                        c.setCellStyle(createCellStyle(workbook,8));
                    }

                });
            }
            //标题行 加入换行
            for (int j = 0; j <= 17; j++) {
                getRow(sheet, 1).getCell(j).setCellStyle(specialCellStyle(workbook, 9, false, false, true, true, true, true, false, true, "", "center"));
            }

            //汇总加粗
            getRow(sheet, normalOutQuotationResVo.getDataList().size() + 2).getCell(0).setCellStyle(specialCellStyle(workbook, 9, true, false, true, true, true, true, false, false, "", "center"));

            //设置边框
            CellRangeAddress region = new CellRangeAddress(0, rowNum + 1, 0, 17);
            // 设置合并区域的边框样式
            RegionUtil.setBorderTop(BorderStyle.THICK, region, sheet);
            RegionUtil.setBorderBottom(BorderStyle.THICK, region, sheet);
            RegionUtil.setBorderLeft(BorderStyle.THICK, region, sheet);
            RegionUtil.setBorderRight(BorderStyle.THICK, region, sheet);
            // 设置边框颜色
            RegionUtil.setTopBorderColor(IndexedColors.BLUE.getIndex(), region, sheet);
            RegionUtil.setRightBorderColor(IndexedColors.BLUE.getIndex(), region, sheet);
            RegionUtil.setBottomBorderColor(IndexedColors.BLUE.getIndex(), region, sheet);
            RegionUtil.setLeftBorderColor(IndexedColors.BLUE.getIndex(), region, sheet);



            //设置新的sheet页
            for (int i = 0; i < normalOutQuotationResVo.getDataList().size(); i++) {
                QrQuotationDetailVo detailVo = normalOutQuotationResVo.getDataList().get(i);
                QrQuotationSubDetailVo detailData = detailVo.getDetailData();
                String sheetName = detailVo.getNameOfcodeOfProduct();
                Sheet newSheet = workbook.createSheet(sheetName);
                newSheet.setZoom(70);
                //调整列宽
                newSheet.setColumnWidth(0, 6 * 256);
                newSheet.setColumnWidth(1, 7 * 256);
                newSheet.setColumnWidth(2, 6 * 256);
                newSheet.setColumnWidth(3, 2 * 256);
                newSheet.setColumnWidth(4, 11 * 256);
                newSheet.setColumnWidth(5, 26 * 256);
                newSheet.setColumnWidth(6, 16 * 256);
                newSheet.setColumnWidth(7, 12 * 256);
                newSheet.setColumnWidth(8, 8 * 256);
                newSheet.setColumnWidth(9, 2 * 256);
                newSheet.setColumnWidth(10, 8 * 256);
                newSheet.setColumnWidth(11, 2 * 256);
                newSheet.setColumnWidth(12, 8 * 256);
                newSheet.setColumnWidth(13, 13 * 256);
                newSheet.setColumnWidth(14, 9 * 256);
                newSheet.setColumnWidth(15, 7 * 256);
                newSheet.setColumnWidth(16, 29 * 256);
                newSheet.setColumnWidth(17, 23 * 256);
                newSheet.setColumnWidth(18, 27 * 256);

                mergedRegion(newSheet, 0, 2, 0, 4, true, "");
                // 设置标题行
                // 获取图片文件的绝对路径
                cellImage(workbook, newSheet, 0, "templates/qirui.png", 0, 0, 5, 3);
                mergedRegion(newSheet, 3, 3, 0, 4, true, "奇瑞汽车股份有限公司");

                mergedRegion(newSheet, 0, 0, 5, 16, false, "模具供应商报价单");
                mergedRegion(newSheet, 1, 2, 5, 16, false, "Mold Quotation Form");
                mergedRegion(newSheet, 3, 3, 5, 16, false, "");

                getCell(getRow(newSheet, 0), 17).setCellValue("联系人/Attention:");
                getCell(getRow(newSheet, 1), 17).setCellValue("");
                mergedRegion(newSheet, 2, 3, 17, 17, true, "电话/TEL:");

                getCell(getRow(newSheet, 0), 18).setCellValue(detailVo.getDetailData().getContacts());
                mergedRegion(newSheet, 1, 1, 17, 18, true, "");
                mergedRegion(newSheet, 2, 3, 18, 18, true, detailVo.getDetailData().getTelphone());
                mergedRegion(newSheet, 4, 4, 0, 18, false, "");


                mergedRegion(newSheet, 5, 10, 7, 15, false, "");

                NumberFormat formatter3 = new DecimalFormat("#0.000");
                List<TempVo> list = Lists.newArrayList();
                list.add(new TempVo("模具名称/Mold Specification", "型腔数量/Cav.", "产品零件号/Part No.", "首次试模时间/T1 Time"));
                String t0Time = "";
                String leadTime ="";
                if(StringUtils.isNotBlank(detailData.getT0Time())){
                    // 将字符串解析为 Date 对象
                    Date date = inputFormat.parse(detailData.getT0Time());
                    // 格式化 Date 对象为字符串
                    t0Time = outputFormat.format(date);
                }

                if(StringUtils.isNotBlank(detailData.getLeadTime())){
                    // 将字符串解析为 Date 对象
                    Date date = inputFormat.parse(detailData.getLeadTime());
                    // 格式化 Date 对象为字符串
                    leadTime = outputFormat.format(date);
                }

                list.add(new TempVo(detailData.getMoldName(), detailData.getCavityCount(), detailData.getCodeOfcodeOfProduct(), t0Time));
                list.add(new TempVo("产品大小/Part Size (mm)", "产品重量/Part Weight(g)", "数据文件名/Drawing No.", "最终交付时间/Lead Time"));
                String productSize = detailData.getLengthOfcodeOfProduct() + "*" + detailData.getWidthOfcodeOfProduct() + "*" + detailData.getHeightOfcodeOfProduct();
                list.add(new TempVo(productSize, formatter3.format(detailData.getWeightOfcodeOfProduct()), detailData.getDrawingNo(), leadTime));
                list.add(new TempVo("外形尺寸/Mold Size (mm)", "模具重量/Mold Weight(Kg)", "模具寿命/Longevity", "设备吨位/Press(Ton)"));
                String moldSize = detailData.getLengthOfShape() + "*" + detailData.getWidthOfShape() + "*" + detailData.getHeightOfShape();
                list.add(new TempVo(moldSize, formatter3.format(detailData.getMoldWeight()), detailData.getLongevity(), detailData.getPressTon()));

                rowNum = 4;
                for (int i1 = 0; i1 < list.size(); i1++) {
                    rowNum = rowNum + 1;
                    TempVo tempVo = list.get(i1);
                    mergedRegion(newSheet, rowNum, rowNum, 0, 4, true, tempVo.getParam1());
                    mergedRegion(newSheet, rowNum, rowNum, 5, 6, true, tempVo.getParam2());
                    mergedRegion(newSheet, rowNum, rowNum, 16, 17, true, tempVo.getParam3());
                    getRow(newSheet, rowNum).createCell(18).setCellValue(tempVo.getParam4());
                }
                mergedRegion(newSheet, 11, 11, 0, 18, false, "");

                rowNum = 12;
                mergedRegion(newSheet, rowNum, 24, 0, 1, true, "加工材料费/  Machining Material Cost\n（A）");
                mergedRegion(newSheet, rowNum, rowNum, 2, 5, true, "材料名称/Material");
                mergedRegion(newSheet, rowNum, rowNum, 6, 7, true, "材料牌号/Specification");
                mergedRegion(newSheet, rowNum, rowNum, 8, 12, true, "尺寸/Size(mm×mm×mm)");
                getRow(newSheet, rowNum).createCell(13).setCellValue("数量/Number");
                mergedRegion(newSheet, rowNum, rowNum, 14, 15, true, "重量/Weight(Kg)");
                getRow(newSheet, rowNum).createCell(16).setCellValue("单价(元)/Unit Price(RMB)");
                getRow(newSheet, rowNum).createCell(17).setCellValue("金额(元)/Price(RMB)");
                getRow(newSheet, rowNum).createCell(18).setCellValue("小计(元)/Subtotal(RMB)");

                MachiningMaterialFee machiningMaterialFee = detailData.getMachiningMaterialFee();
                if (null != machiningMaterialFee && machiningMaterialFee.getDataList().size() > 0) {
                    List<MachiningMaterialFee.DataList> dataList = machiningMaterialFee.getDataList();
                    int size = dataList.size();
                    for (int i1 = 0; i1 < size; i1++) {
                        rowNum = rowNum + 1;
                        MachiningMaterialFee.DataList item = machiningMaterialFee.getDataList().get(i1);
                        mergedRegion(newSheet, rowNum, rowNum, 2, 5, true, item.getMaterial());
                        mergedRegion(newSheet, rowNum, rowNum, 6, 7, true, item.getSpecification());
                        getRow(newSheet, rowNum).createCell(8).setCellValue(String.valueOf(item.getLength()));
                        getRow(newSheet, rowNum).createCell(9).setCellValue("X");
                        getRow(newSheet, rowNum).createCell(10).setCellValue(String.valueOf(item.getWidth()));
                        getRow(newSheet, rowNum).createCell(11).setCellValue("X");
                        getRow(newSheet, rowNum).createCell(12).setCellValue(String.valueOf(item.getHeight()));
                        getRow(newSheet, rowNum).createCell(13).setCellValue(String.valueOf(item.getCount()));
                        mergedRegion(newSheet, rowNum, rowNum, 14, 15, true, formatter3.format(item.getWeight()));

                        getRow(newSheet, rowNum).createCell(16).setCellValue(formatter.format(item.getPrice()));
                        getRow(newSheet, rowNum).createCell(17).setCellValue(formatter.format(item.getTotalPrice()));
                        int lastRow = rowNum + size - 1;
                        if (i1 == 0) {
                            mergedRegion(newSheet, rowNum, lastRow, 18, 18, true, "￥" + BigdecimalUtil.doubleTo2DecimalString(machiningMaterialFee.getSubTotal()));
                        }
                    }
                }
                mergedRegion(newSheet, 25, 25, 0, 18, true, "");

                rowNum = 26;
                mergedRegion(newSheet, rowNum, 29, 0, 1, true, "热处理/Heat Treatment\n(B)");
                mergedRegion(newSheet, rowNum, rowNum, 2, 5, true, "热处理名称/Item");
                mergedRegion(newSheet, rowNum, rowNum, 6, 7, true, "重量/Weight(Kg)");
                mergedRegion(newSheet, rowNum, rowNum, 8, 15, true, "单价(元)/Unit Price(RMB)");
                mergedRegion(newSheet, rowNum, rowNum, 16, 17, true, "金额(元)/Price(RMB)");
                getRow(newSheet, rowNum).createCell(18).setCellValue("小计(元)/Subtotal(RMB)");

                HeatTreatmentFee heatTreatmentFee = detailData.getHeatTreatmentFee();
                if (null != heatTreatmentFee && heatTreatmentFee.getDataList().size() > 0) {
                    List<HeatTreatmentFee.DataList> dataList = heatTreatmentFee.getDataList();
                    int size = dataList.size();
                    for (int i1 = 0; i1 < size; i1++) {
                        rowNum = rowNum + 1;
                        HeatTreatmentFee.DataList item = dataList.get(i1);
                        int lastRow = rowNum + size - 1;
                        mergedRegion(newSheet, rowNum, rowNum, 2, 5, true, item.getItemName());
                        mergedRegion(newSheet, rowNum, rowNum, 6, 7, true, formatter3.format(item.getWeight()));
                        mergedRegion(newSheet, rowNum, rowNum, 8, 15, true, formatter.format(item.getPrice()));
                        mergedRegion(newSheet, rowNum, rowNum, 16, 17, true, formatter.format(item.getTotalPrice()));
                        if (i1 == 0) {
                            mergedRegion(newSheet, rowNum, lastRow, 18, 18, true, "￥" + BigdecimalUtil.doubleTo2DecimalString(heatTreatmentFee.getSubTotal()));
                        }
                    }
                }
                mergedRegion(newSheet, 30, 30, 0, 18, true, "");

                rowNum = 31;
                mergedRegion(newSheet, rowNum, 37, 0, 1, true, "装配材料费/Assemly Material Cost\n(C)");
                mergedRegion(newSheet, rowNum, rowNum, 2, 5, true, "装配件/Item");
                mergedRegion(newSheet, rowNum, rowNum, 6, 7, true, "规格型号/Specification");
                mergedRegion(newSheet, rowNum, rowNum, 8, 11, true, "品牌/Supplier");
                mergedRegion(newSheet, rowNum, rowNum, 12, 15, true, "数量/Number");
                getRow(newSheet, rowNum).createCell(16).setCellValue("单价(元)/Unit Price(RMB)");
                getRow(newSheet, rowNum).createCell(17).setCellValue("金额(元)/Price(RMB)");
                getRow(newSheet, rowNum).createCell(18).setCellValue("小计(元)/Subtotal(RMB)");

                AssemlyMaterialFee assemlyMaterialFee = detailData.getAssemlyMaterialFee();
                if (null != assemlyMaterialFee && assemlyMaterialFee.getDataList().size() > 0) {
                    List<AssemlyMaterialFee.DataList> dataList = assemlyMaterialFee.getDataList();
                    int size = dataList.size();
                    for (int i1 = 0; i1 < size; i1++) {
                        rowNum = rowNum + 1;
                        AssemlyMaterialFee.DataList item = dataList.get(i1);
                        int lastRow = rowNum + size - 1;
                        mergedRegion(newSheet, rowNum, rowNum, 2, 5, true, item.getItemName());
                        mergedRegion(newSheet, rowNum, rowNum, 6, 7, true, item.getSpecification());
                        mergedRegion(newSheet, rowNum, rowNum, 8, 11, true, item.getSupplie());
                        mergedRegion(newSheet, rowNum, rowNum, 12, 15, true, String.valueOf(item.getCount()));
                        getRow(newSheet, rowNum).createCell(16).setCellValue(formatter.format(item.getPrice()));
                        getRow(newSheet, rowNum).createCell(17).setCellValue(formatter.format(item.getTotalPrice()));
                        if (i1 == 0) {
                            mergedRegion(newSheet, rowNum, lastRow, 18, 18, true, "￥" + BigdecimalUtil.doubleTo2DecimalString(assemlyMaterialFee.getSubTotal()));
                        }
                    }
                }
                mergedRegion(newSheet, 38, 38, 0, 18, true, "");


                rowNum = 39;
                mergedRegion(newSheet, rowNum, 41, 0, 1, true, "设计费/Design Cost\n(D)");
                mergedRegion(newSheet, rowNum, rowNum, 2, 5, true, "名称/Item");
                mergedRegion(newSheet, rowNum, rowNum, 6, 7, true, "工时(小时)/Hour");
                mergedRegion(newSheet, rowNum, rowNum, 8, 15, true, "单价(元)/Unit Price(RMB)");
                mergedRegion(newSheet, rowNum, rowNum, 16, 17, true, "金额(元)/Price(RMB)");
                getRow(newSheet, rowNum).createCell(18).setCellValue("小计(元)/Subtotal(RMB)");

                DesignFee designFee = detailData.getDesignFee();
                if (null != designFee && designFee.getDataList().size() > 0) {
                    List<DesignFee.DataList> dataList = designFee.getDataList();
                    int size = dataList.size();
                    for (int i1 = 0; i1 < size; i1++) {
                        rowNum = rowNum + 1;
                        DesignFee.DataList item = dataList.get(i1);
                        int lastRow = rowNum + size - 1;
                        mergedRegion(newSheet, rowNum, rowNum, 2, 5, true, item.getItemName());
                        mergedRegion(newSheet, rowNum, rowNum, 6, 7, true, String.valueOf(item.getHour()));
                        mergedRegion(newSheet, rowNum, rowNum, 8, 15, true, formatter.format(item.getPrice()));
                        mergedRegion(newSheet, rowNum, rowNum, 16, 17, true, formatter.format(item.getTotalPrice()));
                        if (i1 == 0) {
                            mergedRegion(newSheet, rowNum, lastRow, 18, 18, true, "￥" + BigdecimalUtil.doubleTo2DecimalString(designFee.getSubTotal()));
                        }
                    }
                }
                mergedRegion(newSheet, 42, 42, 0, 18, true, "");

                rowNum = 43;
                mergedRegion(newSheet, rowNum, 54, 0, 1, true, "加工费/   Manufacturing Cost\n(E)");
                mergedRegion(newSheet, rowNum, rowNum, 2, 5, true, "名称/Item");
                mergedRegion(newSheet, rowNum, rowNum, 6, 7, true, "工时(小时)/Hour");
                mergedRegion(newSheet, rowNum, rowNum, 8, 15, true, "单价(元)/Unit Price(RMB)");
                mergedRegion(newSheet, rowNum, rowNum, 16, 17, true, "金额(元)/Price(RMB)");
                getRow(newSheet, rowNum).createCell(18).setCellValue("小计(元)/Subtotal(RMB)");

                ManufacturingFee manufacturingFee = detailData.getManufacturingFee();
                if (null != manufacturingFee && manufacturingFee.getDataList().size() > 0) {
                    List<ManufacturingFee.DataList> dataList = manufacturingFee.getDataList();
                    int size = dataList.size();
                    for (int i1 = 0; i1 < size; i1++) {
                        rowNum = rowNum + 1;
                        ManufacturingFee.DataList item = dataList.get(i1);
                        int lastRow = rowNum + size - 1;
                        mergedRegion(newSheet, rowNum, rowNum, 2, 5, true, item.getItemName());
                        mergedRegion(newSheet, rowNum, rowNum, 6, 7, true, String.valueOf(item.getHour()));
                        mergedRegion(newSheet, rowNum, rowNum, 8, 15, true, formatter.format(item.getPrice()));
                        mergedRegion(newSheet, rowNum, rowNum, 16, 17, true, formatter.format(item.getTotalPrice()));
                        if (i1 == 0) {
                            mergedRegion(newSheet, rowNum, lastRow, 18, 18, true, "￥" + BigdecimalUtil.doubleTo2DecimalString(manufacturingFee.getSubTotal()));
                        }
                    }
                }
                mergedRegion(newSheet, 55, 55, 0, 18, true, "");

                rowNum = 56;
                mergedRegion(newSheet, rowNum, 58, 0, 1, true, "三坐标测量费CMM Measure(F)");
                mergedRegion(newSheet, rowNum, rowNum, 2, 5, true, "名称/Item");
                mergedRegion(newSheet, rowNum, rowNum, 6, 7, true, "工时(小时)/Hour");
                mergedRegion(newSheet, rowNum, rowNum, 8, 15, true, "单价(元)/Unit Price(RMB)");
                mergedRegion(newSheet, rowNum, rowNum, 16, 17, true, "金额(元)/Price(RMB)");
                getRow(newSheet, rowNum).createCell(18).setCellValue("小计(元)/Subtotal(RMB)");

                QrCMMeasure cmmMeasure = detailData.getCMMMeasure();
                if (null != cmmMeasure && cmmMeasure.getDataList().size() > 0) {
                    List<QrCMMeasure.DataList> dataList = cmmMeasure.getDataList();
                    int size = dataList.size();
                    for (int i1 = 0; i1 < size; i1++) {
                        rowNum = rowNum + 1;
                        QrCMMeasure.DataList item = dataList.get(i1);
                        int lastRow = rowNum + size - 1;
                        mergedRegion(newSheet, rowNum, rowNum, 2, 5, true, item.getItemName());
                        mergedRegion(newSheet, rowNum, rowNum, 6, 7, true, String.valueOf(item.getHour()));
                        mergedRegion(newSheet, rowNum, rowNum, 8, 15, true, formatter.format(item.getPrice()));
                        mergedRegion(newSheet, rowNum, rowNum, 16, 17, true, formatter.format(item.getTotalPrice()));
                        if (i1 == 0) {
                            mergedRegion(newSheet, rowNum, lastRow, 18, 18, true, "￥" + BigdecimalUtil.doubleTo2DecimalString(cmmMeasure.getSubTotal()));
                        }
                    }
                }
                mergedRegion(newSheet, 59, 59, 0, 18, true, "");

                rowNum = 60;
                mergedRegion(newSheet, rowNum, 64, 0, 1, true, "其它费用/Other Fee\n(G=H+I+J+K)");
                mergedRegion(newSheet, rowNum, rowNum, 2, 5, true, "费用名称/Item");
                mergedRegion(newSheet, rowNum, rowNum, 6, 15, true, "费用计算说明/Descripition");
                mergedRegion(newSheet, rowNum, rowNum, 16, 17, true, "金额(元)/Price(RMB)");
                getRow(newSheet, rowNum).createCell(18).setCellValue("小计(元)/Subtotal(RMB)");

                OtherFee otherFee = detailData.getOtherFee();
                if (null != otherFee && otherFee.getDataList().size() > 0) {
                    List<OtherFee.DataList> dataList = otherFee.getDataList();
                    int size = dataList.size();
                    for (int i1 = 0; i1 < size; i1++) {
                        rowNum = rowNum + 1;
                        OtherFee.DataList item = dataList.get(i1);
                        int lastRow = rowNum + size - 1;
                        mergedRegion(newSheet, rowNum, rowNum, 2, 5, true, item.getItemName());
                        mergedRegion(newSheet, rowNum, rowNum, 6, 15, true, item.getFeeDesc());
                        mergedRegion(newSheet, rowNum, rowNum, 16, 17, true, formatter.format(item.getTotalPrice()));
                        if (i1 == 0) {
                            mergedRegion(newSheet, rowNum, lastRow, 18, 18, true, "￥" + BigdecimalUtil.doubleTo2DecimalString(otherFee.getSubTotal()));
                        }
                    }
                }
                mergedRegion(newSheet, 65, 65, 0, 18, true, "");
                mergedRegion(newSheet, 66, 68, 0, 5, true, "模具总价(万元)/Mold Price(RMB):\nT0=(A+B+C+D+E+F+G)");


                BigDecimal moldTotalPrice = new BigDecimal(0.0);
                if (null != machiningMaterialFee) {
                    moldTotalPrice = moldTotalPrice.add(new BigDecimal(machiningMaterialFee.getSubTotal()));
                }
                if (null != heatTreatmentFee) {
                    moldTotalPrice = moldTotalPrice.add(new BigDecimal(heatTreatmentFee.getSubTotal()));
                }
                if (null != assemlyMaterialFee) {
                    moldTotalPrice = moldTotalPrice.add(new BigDecimal(assemlyMaterialFee.getSubTotal()));
                }
                if (null != designFee) {
                    moldTotalPrice = moldTotalPrice.add(new BigDecimal(designFee.getSubTotal()));
                }
                if (null != manufacturingFee) {
                    moldTotalPrice = moldTotalPrice.add(new BigDecimal(manufacturingFee.getSubTotal()));
                }
                if (null != cmmMeasure) {
                    moldTotalPrice = moldTotalPrice.add(new BigDecimal(cmmMeasure.getSubTotal()));
                }
                if (null != otherFee) {
                    moldTotalPrice = moldTotalPrice.add(new BigDecimal(otherFee.getSubTotal()));
                }
                mergedRegion(newSheet, 66, 68, 6, 18, true,
                        "￥" + BigdecimalUtil.bigDecimalTo4DecimalString(moldTotalPrice.divide(new BigDecimal("10000"))));

                mergedRegion(newSheet, 69, 69, 0, 18, true, "");
                // 创建两种字体样式，分别用于不同部分的文本
                fontBold = workbook.createFont();
                fontBold.setBold(true);
                fontBold.setFontName("宋体");
                fontBold.setFontHeightInPoints((short) 12);//设置字号
                //设置字体颜色
                fontBold.setColor(IndexedColors.BLUE.index);

                fontNormal = workbook.createFont();
                fontNormal.setBold(true);
                fontNormal.setFontName("宋体");
                fontNormal.setFontHeightInPoints((short) 12);//设置字号
                //设置字体颜色
                fontNormal.setColor(IndexedColors.RED.index);

                richTextString = new XSSFRichTextString();
                richTextString.append("备注：1、原材按照产品实际模具耗材尺寸报价，2、原材报价采购的是粗料和精料需要明确；", fontBold);// 使用粗体字体
                richTextString.append("3、以上单项报价全部含13%增值税;4、不符合以上格式报价无效。", fontNormal); // 使用普通字体

                //设置空白背景 无边框
                for (int j = 70; j <= 75; j++) {
                    for (int k = 0; k <= 18; k++) {
                        getCell(getRow(newSheet, j), k).setCellStyle(removeBorder(workbook));
                    }
                }
                getRow(newSheet, 71).createCell(1).setCellValue("供应商/Supplier：" + detailData.getSupplier());
                getRow(newSheet, 71).createCell(14).setCellValue("签字/Signature:  " + detailData.getSignature());
                getRow(newSheet, 73).createCell(1).setCellValue("盖章/Stamp:" + detailData.getStamp());
                getRow(newSheet, 73).createCell(14).setCellValue("日期/Date:  " + detailData.getDate());

                //处理列表展示样式
                for (int j = 0; j <= 69; j++) {
                    getRow(newSheet, j).forEach(c -> {
                        if (c.getRowIndex() != 5) {
                            c.setCellStyle(createCellStyle(workbook,12));
                        } else {
                            if (c.getColumnIndex() >= 7 && c.getColumnIndex() <= 15) {

                            } else {
                                c.setCellStyle(createCellStyle(workbook,12));
                            }
                        }

                    });
                }
                //赋值
                getRow(newSheet, 69).createCell(0).setCellValue(richTextString);
                for (int j = 0; j < 19; j++) {
                    getCell(getRow(newSheet, 69), j).setCellStyle(specialCellStyle(workbook, 24, true, false, true, true, true, false, false, false, "blue", "left"));
                }

                //奇瑞汽车股份有限公司 加粗
                getRow(newSheet, 3).getCell(0).setCellStyle(specialCellStyle(workbook, 15, true, false, false, true, false, false, false, false, "", "center"));

                //自定义样式处理 设置字体为蓝色
                getRow(newSheet, 0).getCell(5).setCellStyle(specialCellStyle(workbook, 24, true, false, false, true, false, false, false, false, "blue", "center"));
                getRow(newSheet, 1).getCell(5).setCellStyle(specialCellStyle(workbook, 20, true, false, false, true, false, false, false, false, "blue", "center"));
                getRow(newSheet, 3).getCell(5).setCellStyle(specialCellStyle(workbook, 20, true, false, true, true, false, false, false, false, "blue", "center"));
                getRow(newSheet, 3).createCell(6).setCellStyle(specialCellStyle(workbook, 20, true, false, true, true, false, false, false, false, "blue", "center"));
                getRow(newSheet, 3).createCell(7).setCellStyle(specialCellStyle(workbook, 20, true, false, true, true, false, false, false, false, "blue", "center"));
                getRow(newSheet, 3).createCell(8).setCellStyle(specialCellStyle(workbook, 20, true, false, true, true, false, false, false, false, "blue", "center"));
                getRow(newSheet, 3).createCell(9).setCellStyle(specialCellStyle(workbook, 20, true, false, true, true, false, false, false, false, "blue", "center"));
                getRow(newSheet, 3).createCell(10).setCellStyle(specialCellStyle(workbook, 20, true, false, true, true, false, false, false, false, "blue", "center"));
                getRow(newSheet, 3).createCell(11).setCellStyle(specialCellStyle(workbook, 20, true, false, true, true, false, false, false, false, "blue", "center"));
                getRow(newSheet, 3).createCell(12).setCellStyle(specialCellStyle(workbook, 20, true, false, true, true, false, false, false, false, "blue", "center"));
                getRow(newSheet, 3).createCell(13).setCellStyle(specialCellStyle(workbook, 20, true, false, true, true, false, false, false, false, "blue", "center"));
                getRow(newSheet, 3).createCell(14).setCellStyle(specialCellStyle(workbook, 20, true, false, true, true, false, false, false, false, "blue", "center"));
                getRow(newSheet, 3).createCell(15).setCellStyle(specialCellStyle(workbook, 20, true, false, true, true, false, false, false, false, "blue", "center"));
                getRow(newSheet, 3).createCell(16).setCellStyle(specialCellStyle(workbook, 20, true, false, true, true, false, false, false, false, "blue", "center"));

                //加背景色设置为灰色
                getRow(newSheet, 0).getCell(17).setCellStyle(specialCellStyle(workbook, 12, false, true, true, true, true, true, true, false, "", "center"));
                getRow(newSheet, 2).getCell(17).setCellStyle(specialCellStyle(workbook, 12, false, true, true, true, true, true, true, false, "", "center"));
                getRow(newSheet, 5).getCell(0).setCellStyle(specialCellStyle(workbook, 12, false, true, true, true, true, true, true, false, "", "center"));
                getRow(newSheet, 5).getCell(5).setCellStyle(specialCellStyle(workbook, 12, false, true, true, true, true, true, true, false, "", "center"));
                getRow(newSheet, 5).getCell(16).setCellStyle(specialCellStyle(workbook, 12, false, true, true, true, true, true, true, false, "", "center"));
                getRow(newSheet, 5).getCell(18).setCellStyle(specialCellStyle(workbook, 12, false, true, true, true, true, true, true, false, "", "center"));
                getRow(newSheet, 7).getCell(0).setCellStyle(specialCellStyle(workbook, 12, false, true, true, true, true, true, true, false, "", "center"));
                getRow(newSheet, 7).getCell(5).setCellStyle(specialCellStyle(workbook, 12, false, true, true, true, true, true, true, false, "", "center"));
                getRow(newSheet, 7).getCell(16).setCellStyle(specialCellStyle(workbook, 12, false, true, true, true, true, true, true, false, "", "center"));
                getRow(newSheet, 7).getCell(18).setCellStyle(specialCellStyle(workbook, 12, false, true, true, true, true, true, true, false, "", "center"));
                getRow(newSheet, 9).getCell(0).setCellStyle(specialCellStyle(workbook, 12, false, true, true, true, true, true, true, false, "", "center"));
                getRow(newSheet, 9).getCell(5).setCellStyle(specialCellStyle(workbook, 12, false, true, true, true, true, true, true, false, "", "center"));
                getRow(newSheet, 9).getCell(16).setCellStyle(specialCellStyle(workbook, 12, false, true, true, true, true, true, true, false, "", "center"));
                getRow(newSheet, 9).getCell(18).setCellStyle(specialCellStyle(workbook, 12, false, true, true, true, true, true, true, false, "", "center"));
                getRow(newSheet, 12).forEach(cell -> {
                    boolean isWrapText = false;
                    if (cell.getColumnIndex() == 0) {
                        isWrapText = true;
                    }
                    if (cell.getColumnIndex() == 0) {
                        isWrapText = true;
                    }
                    cell.setCellStyle(specialCellStyle(workbook, 12, false, true, true, true, true, true, true, isWrapText, "", "center"));
                });
                getRow(newSheet, 26).forEach(cell -> {
                    boolean isWrapText = false;
                    if (cell.getColumnIndex() == 0) {
                        isWrapText = true;
                    }
                    cell.setCellStyle(specialCellStyle(workbook, 12, false, true, true, true, true, true, true, isWrapText, "", "center"));
                });
                getRow(newSheet, 31).forEach(cell -> {
                    boolean isWrapText = false;
                    if (cell.getColumnIndex() == 0) {
                        isWrapText = true;
                    }
                    cell.setCellStyle(specialCellStyle(workbook, 12, false, true, true, true, true, true, true, isWrapText, "", "center"));
                });
                getRow(newSheet, 39).forEach(cell -> {
                    boolean isWrapText = false;
                    if (cell.getColumnIndex() == 0) {
                        isWrapText = true;
                    }
                    cell.setCellStyle(specialCellStyle(workbook, 12, false, true, true, true, true, true, true, isWrapText, "", "center"));
                });
                getRow(newSheet, 43).forEach(cell -> {
                    boolean isWrapText = false;
                    if (cell.getColumnIndex() == 0) {
                        isWrapText = true;
                    }
                    cell.setCellStyle(specialCellStyle(workbook, 12, false, true, true, true, true, true, true, isWrapText, "", "center"));
                });
                getRow(newSheet, 56).forEach(cell -> {
                    boolean isWrapText = false;
                    if (cell.getColumnIndex() == 0) {
                        isWrapText = true;
                    }
                    cell.setCellStyle(specialCellStyle(workbook, 12, false, true, true, true, true, true, true, isWrapText, "", "center"));
                });
                getRow(newSheet, 60).forEach(cell -> {
                    boolean isWrapText = false;
                    if (cell.getColumnIndex() == 0) {
                        isWrapText = true;
                    }
                    cell.setCellStyle(specialCellStyle(workbook, 12, false, true, true, true, true, true, true, isWrapText, "", "center"));
                });

                for (int j = 13; j < 65; j++) {
                    if (j != 25 && j != 26 && j != 30 && j != 31 && j != 38 && j != 39 && j != 42 && j != 43 && j != 55 && j != 56 && j != 59 && j != 60 && j != 65 && j != 66) {
                        getRow(newSheet, j).getCell(2).setCellStyle(specialCellStyle(workbook, 12, false, true, true, true, false, false, true, false, "", "left"));
                        getRow(newSheet, j).getCell(17).setCellStyle(specialCellStyle(workbook, 12, false, true, true, true, true, true, true, false, "", "center"));
                        if ((j > 26 && j < 30) || (j > 39 && j < 42) || (j > 43 && j < 55 || (j > 56 && j < 59 || (j > 60 && j < 63)))) {
                            getRow(newSheet, j).getCell(16).setCellStyle(specialCellStyle(workbook, 12, false, true, true, true, true, true, true, false, "", "center"));
                        }
                        getRow(newSheet, j).getCell(18).setCellStyle(specialCellStyle(workbook, 12, false, true, true, true, true, true, true, false, "blue", "center"));

                    }
                }

                //设置模具总价行 样式
                getRow(newSheet, 66).getCell(0).setCellStyle(specialCellStyle(workbook, 12, false, true, false, false, false, false, true, true, "blue", "center"));
                getRow(newSheet, 66).getCell(6).setCellStyle(specialCellStyle(workbook, 12, false, true, false, false, false, false, true, false, "blue", "center"));

                getRow(newSheet, 71).getCell(1).setCellStyle(specialCellStyle(workbook, 16, true, true, false, false, false, false, false, false, "blue", "left"));
                getRow(newSheet, 71).getCell(14).setCellStyle(specialCellStyle(workbook, 16, true, true, false, false, false, false, false, false, "blue", "left"));
                getRow(newSheet, 73).getCell(1).setCellStyle(specialCellStyle(workbook, 16, true, true, false, false, false, false, false, false, "blue", "left"));
                getRow(newSheet, 73).getCell(14).setCellStyle(specialCellStyle(workbook, 16, true, true, false, false, false, false, false, false, "blue", "left"));


                //设置边框
                region = new CellRangeAddress(70, 75, 0, 18);
                // 设置合并区域的边框样式
                RegionUtil.setBorderTop(BorderStyle.THIN, region, newSheet);
                RegionUtil.setBorderBottom(BorderStyle.THIN, region, newSheet);
                RegionUtil.setBorderLeft(BorderStyle.THIN, region, newSheet);
                RegionUtil.setBorderRight(BorderStyle.THIN, region, newSheet);
                //设置边框颜色
                RegionUtil.setTopBorderColor(IndexedColors.BLACK.getIndex(), region, newSheet);
                RegionUtil.setRightBorderColor(IndexedColors.BLACK.getIndex(), region, newSheet);
                RegionUtil.setBottomBorderColor(IndexedColors.BLACK.getIndex(), region, newSheet);
                RegionUtil.setLeftBorderColor(IndexedColors.BLACK.getIndex(), region, newSheet);


                newSheet.getRow(0).setHeight((short) (33.65 * 20));
                newSheet.getRow(1).setHeight((short) (14.05 * 20));
                newSheet.getRow(2).setHeight((short) (14.05 * 20));
                newSheet.getRow(3).setHeight((short) (14.05 * 20));
                newSheet.getRow(4).setHeight((short) (6.10 * 20));
                newSheet.getRow(5).setHeight((short) (22.00 * 20));
                newSheet.getRow(6).setHeight((short) (22.00 * 20));
                newSheet.getRow(7).setHeight((short) (22.00 * 20));
                newSheet.getRow(8).setHeight((short) (22.00 * 20));
                newSheet.getRow(9).setHeight((short) (22.00 * 20));
                newSheet.getRow(10).setHeight((short) (22.00 * 20));
                newSheet.getRow(11).setHeight((short) (6.10 * 20));
                for (int j = 12; j < 25; j++) {
                    newSheet.getRow(j).setHeight((short) (22.00 * 20));
                }
                newSheet.getRow(25).setHeight((short) (6.10 * 20));
                for (int j = 26; j < 30; j++) {
                    newSheet.getRow(j).setHeight((short) (22.00 * 20));
                }

                newSheet.getRow(30).setHeight((short) (6.10 * 20));
                for (int j = 31; j < 38; j++) {
                    newSheet.getRow(j).setHeight((short) (22.00 * 20));
                }
                newSheet.getRow(38).setHeight((short) (6.10 * 20));
                for (int j = 39; j < 42; j++) {
                    newSheet.getRow(j).setHeight((short) (22.00 * 20));
                }
                newSheet.getRow(42).setHeight((short) (6.10 * 20));
                for (int j = 43; j < 55; j++) {
                    newSheet.getRow(j).setHeight((short) (22.00 * 20));
                }
                newSheet.getRow(55).setHeight((short) (6.10 * 20));
                for (int j = 56; j < 59; j++) {
                    newSheet.getRow(j).setHeight((short) (22.00 * 20));
                }
                newSheet.getRow(59).setHeight((short) (6.10 * 20));
                for (int j = 60; j < 65; j++) {
                    newSheet.getRow(j).setHeight((short) (22.00 * 20));
                }
                newSheet.getRow(65).setHeight((short) (6.10 * 20));
                for (int j = 66; j < 69; j++) {
                    newSheet.getRow(j).setHeight((short) (22.00 * 20));
                }
                newSheet.getRow(69).setHeight((short) (37.3 * 20));
                for (int j = 70; j < 76; j++) {
                    newSheet.getRow(j).setHeight((short) (16.5 * 20));
                }


            }
            //输出文件
            PoiUtils.createExcelFile(workbook, filePath, fileName);
            log.info("奇瑞对外报价单导出end");
        } catch (Exception e) {
            log.error("奇瑞对外报价单失败", e.getMessage());
            throw new RuntimeException(e);
        }


        return resultPath;
    }

    /**
     * 特殊样式
     *
     * @param workbook
     * @param fontSize
     * @param isBold
     * @return
     */
    private XSSFCellStyle specialCellStyle(XSSFWorkbook workbook, int fontSize, boolean isBold, boolean fillPattern, boolean isBottom, boolean isLeft, boolean isRight, boolean isTop, boolean isForeground, boolean isWrapText, String color, String align) {
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        if (fillPattern) {
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        } else {
            cellStyle.setFillPattern(FillPatternType.NO_FILL);
        }
        if (isBottom) {
            cellStyle.setBorderBottom(BorderStyle.THIN);//下边框
        } else {
            cellStyle.setBorderBottom(BorderStyle.NONE);//下边框
        }
        if (isLeft) {
            cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        } else {
            cellStyle.setBorderLeft(BorderStyle.NONE);//左边框
        }
        if (isRight) {
            cellStyle.setBorderRight(BorderStyle.THIN);//右边框
        } else {
            cellStyle.setBorderRight(BorderStyle.NONE);//右边框
        }
        if (isTop) {
            cellStyle.setBorderTop(BorderStyle.THIN);//上边框
        } else {
            cellStyle.setBorderTop(BorderStyle.NONE);//上边框
        }

        if ("center".equals(align)) {
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
        } else {
            cellStyle.setAlignment(HorizontalAlignment.LEFT);
        }
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setWrapText(isWrapText);

        XSSFFont font = workbook.createFont();
        //设置字体名称
        font.setFontName("宋体");
        font.setBold(isBold);
        font.setFontHeightInPoints((short) fontSize);//设置字号

        if (isForeground) {
            //自定义颜色对象
            cellStyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(242, 242, 242)));
        }


        //设置字体颜色
        if ("blue".equals(color)) {
            font.setColor(IndexedColors.BLUE.index);
        } else if ("red".equals(color)) {
            font.setColor(IndexedColors.RED.index);
        } else {
            font.setColor(IndexedColors.BLACK.index);
        }
        cellStyle.setFont(font);

        return cellStyle;
    }


    /**
     * 自定义样式
     *
     * @param workbook
     * @param fontSize
     * @param isBold
     * @param isWrapText
     * @param color      字体颜色
     * @param isBorder   是否设置边框
     * @return
     */
    private XSSFCellStyle customCellStyle(XSSFWorkbook workbook, int fontSize, boolean isBold, boolean isWrapText, String color, boolean isBorder) {
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        if (isBorder) {
            cellStyle.setFillPattern(FillPatternType.NO_FILL);
        } else {
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            cellStyle.setBorderBottom(BorderStyle.NONE);//下边框
            cellStyle.setBorderLeft(BorderStyle.NONE);//左边框
            cellStyle.setBorderRight(BorderStyle.NONE);//右边框
            cellStyle.setBorderTop(BorderStyle.NONE); //上边框
        }
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setWrapText(isWrapText);

        cellStyle.setFillForegroundColor((short) 12);
        XSSFFont font = workbook.createFont();
        //设置字体名称
        font.setFontName("宋体");
        font.setBold(isBold);
        font.setFontHeightInPoints((short) fontSize);//设置字号
        //设置字体颜色
        if ("blue".equals(color)) {
            font.setColor(IndexedColors.BLUE.index);
        } else if ("red".equals(color)) {
            font.setColor(IndexedColors.RED.index);
        } else {
            font.setColor(IndexedColors.BLACK.index);
        }


        cellStyle.setFont(font);
        return cellStyle;
    }

    private Cell getCell(Row row, int firstCell) {
        return row.getCell(firstCell) == null ? row.createCell(firstCell) : row.getCell(firstCell);
    }

    /**
     * 合并单元格
     *
     * @param sheet
     * @param firstRow
     * @param lastRow
     * @param firstCol
     * @param lastCol
     * @param cellValue
     */
    private void mergedRegion(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol, boolean isStyle, String cellValue) {
        // 合并单元格
        CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        sheet.addMergedRegion(region);
        if (isStyle) {
            //设置样式
            regionStyle(sheet, region);
        }
        //赋值
        getRow(sheet, firstRow).createCell(firstCol).setCellValue(cellValue);
    }

    private void mergedRegionNoStyle(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol, String cellValue) {
        // 合并单元格
        CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        sheet.addMergedRegion(region);
        //赋值
        getRow(sheet, firstRow).createCell(firstCol).setCellValue(cellValue);
    }

    /**
     * 获取行
     *
     * @param sheet
     * @param firstRow
     * @return
     */
    private Row getRow(Sheet sheet, int firstRow) {
        return sheet.getRow(firstRow) == null ? sheet.createRow(firstRow) : sheet.getRow(firstRow);
    }

    /**
     * 设置区域样式
     *
     * @param sheet
     * @param cellAddresses
     */
    private void regionStyle(Sheet sheet, CellRangeAddress cellAddresses) {
        // 设置合并区域的边框样式
        RegionUtil.setBorderTop(BorderStyle.THIN, cellAddresses, sheet);
        RegionUtil.setBorderBottom(BorderStyle.THIN, cellAddresses, sheet);
        RegionUtil.setBorderLeft(BorderStyle.THIN, cellAddresses, sheet);
        RegionUtil.setBorderRight(BorderStyle.THIN, cellAddresses, sheet);
    }

    /**
     * 设置单元格样式
     */
    private XSSFCellStyle createCellStyle(XSSFWorkbook workbook,int fontSize) {
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //加边框
        cellStyle.setBorderBottom(BorderStyle.THIN);//下边框
        cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle.setBorderRight(BorderStyle.THIN);//右边框
        cellStyle.setBorderTop(BorderStyle.THIN); //上边框

        XSSFFont font = workbook.createFont();
        //设置字体名称
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) fontSize);//设置字号
        cellStyle.setFont(font);

        return cellStyle;
    }

    private XSSFCellStyle removeBorder(XSSFWorkbook workbook) {
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        // 设置默认边框样式
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);

        // 移除边框样式
        cellStyle.setBorderBottom(BorderStyle.NONE);
        cellStyle.setBorderLeft(BorderStyle.NONE);
        cellStyle.setBorderRight(BorderStyle.NONE);
        cellStyle.setBorderTop(BorderStyle.NONE);
        return cellStyle;
    }


    //Excel单元格插入图片
    public void cellImage(Workbook wb, Sheet sheet, int i, String imageUrl, int firstCol, int firstRow, int lastCol, int lastRow) {
        BufferedImage bufferImg = null;//图片
        try (InputStream inputStream = getClass().getClassLoader().getResourceAsStream(imageUrl);
             // 先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray
             ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream()) {
            //将图片读到BufferedImage
            bufferImg = ImageIO.read(inputStream);
            // 将图片写入流中
            ImageIO.write(bufferImg, "png", byteArrayOut);
            // 利用HSSFPatriarch将图片写入EXCEL
            Drawing patriarch = sheet.createDrawingPatriarch();
            /**
             * 该构造函数有8个参数
             * 前四个参数是控制图片在单元格的位置，分别是图片距离单元格left，top，right，bottom的像素距离
             * 后四个参数，前连个表示图片左上角所在的cellNum和 rowNum，后天个参数对应的表示图片右下角所在的cellNum和 rowNum，
             * excel中的cellNum和rowNum的index都是从0开始的
             *
             */
            //图片一导出到单元格B5中
            XSSFClientAnchor anchor = new XSSFClientAnchor(20, 2, 20, 2, firstCol, firstRow, lastCol, lastRow);
            // 插入图片
            patriarch.createPicture(anchor, wb.addPicture(byteArrayOut
                    .toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));
        } catch (IOException e) {
            log.error("获取图片失败：{}" + imageUrl);
            e.printStackTrace();
        }
    }


}
